package qimao.com.edu.dao.impl;

import qimao.com.edu.dao.BaseDao;
import qimao.com.edu.dao.CategoryDao;
import qimao.com.edu.entity.CategoryMajor;
import qimao.com.edu.entity.CategoryMinor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImpl implements CategoryDao {

    @Override
    public int insMajorCategory(CategoryMajor major) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "INSERT INTO category_major (major_name) VALUES (?)";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setString(1, major.getMajorName());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
    }

    @Override
    public int updMajorCategory(CategoryMajor major) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "UPDATE category_major SET major_name = ? WHERE major_id = ?";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setString(1, major.getMajorName());
            ps.setInt(2, major.getMajorId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
    }

    @Override
    public int delMajorCategory(Integer majorId) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "DELETE FROM category_major WHERE major_id = ?";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, majorId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
        
    }

    @Override
    public CategoryMajor selMajorCategory(Integer majorId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM category_major WHERE major_id = ?";
        CategoryMajor major = null;
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, majorId);
            rs = ps.executeQuery();
            
            if (rs.next()) {
                major = new CategoryMajor();
                major.setMajorId(rs.getInt("major_id"));
                major.setMajorName(rs.getString("major_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(con, ps, rs);
        }
        
        return major;
    }

    @Override
    public int insMinorCategory(CategoryMinor minor) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "INSERT INTO category_minor (parent_id, minor_name) VALUES (?, ?)";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, minor.getParent().getMajorId());
            ps.setString(2, minor.getMinorName());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
    }

    @Override
    public int updMinorCategory(CategoryMinor minor) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "UPDATE category_minor SET parent_id = ?, minor_name = ? WHERE minor_id = ?";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, minor.getParent().getMajorId());
            ps.setString(2, minor.getMinorName());
            ps.setInt(3, minor.getMinorId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
    }

    @Override
    public int delMinorCategory(Integer minorId) {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "DELETE FROM category_minor WHERE minor_id = ?";
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, minorId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            BaseDao.closeAll(con, ps, null);
        }
    }

    @Override
    public CategoryMinor selMinorCategory(Integer minorId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM category_minor WHERE minor_id = ?";
        CategoryMinor minor = null;
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, minorId);
            rs = ps.executeQuery();
            
            if (rs.next()) {
                minor = new CategoryMinor();
                minor.setMinorId(rs.getInt("minor_id"));
                minor.setMinorName(rs.getString("minor_name"));
                
                // 查询父类信息
                Integer parentId = rs.getInt("parent_id");
                CategoryMajor parent = selMajorCategory(parentId);
                minor.setParent(parent);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(con, ps, rs);
        }
        
        return minor;
    }

    @Override
    public List<CategoryMinor> selMinorCategoriesByParent(Integer parentId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM category_minor WHERE parent_id = ?";
        List<CategoryMinor> minors = new ArrayList<>();
        CategoryMajor parent = selMajorCategory(parentId);
        
        try {
            con = BaseDao.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, parentId);
            rs = ps.executeQuery();
            
            while (rs.next()) {
                CategoryMinor minor = new CategoryMinor();
                minor.setMinorId(rs.getInt("minor_id"));
                minor.setParent(parent);
                minor.setMinorName(rs.getString("minor_name"));
                minors.add(minor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(con, ps, rs);
        }
        
        return minors;
    }

	@Override
	public int delMinorCategoriesByParent(Integer parentId) {
		Connection con = null;
	    PreparedStatement ps = null;
	    String sql = "DELETE FROM category_minor WHERE parent_id = ?";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        ps.setInt(1, parentId);
	        return ps.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return 0;
	    } finally {
	        BaseDao.closeAll(con, ps, null);
	    }
	}

	@Override
	public List<CategoryMajor> selAllMajorCategories() {
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    List<CategoryMajor> majorList = new ArrayList<>();
	    String sql = "SELECT * FROM category_major";
	    
	    try {
	        conn = BaseDao.getCon();
	        stmt = conn.prepareStatement(sql);
	        rs = stmt.executeQuery();
	        
	        while (rs.next()) {
	            CategoryMajor major = new CategoryMajor();
	            major.setMajorId(rs.getInt("major_id"));
	            major.setMajorName(rs.getString("major_name"));
	            majorList.add(major);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(conn, stmt, rs);
	    }
	    
	    return majorList;
	}

	@Override
	public List<CategoryMinor> selAllMinorCategories() {
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    List<CategoryMinor> mainorList = new ArrayList<>();
	    String sql = "SELECT * FROM category_minor";
	    
	    try {
	        conn = BaseDao.getCon();
	        stmt = conn.prepareStatement(sql);
	        rs = stmt.executeQuery();
	        
	        while (rs.next()) {
	            CategoryMinor mainor = new CategoryMinor();
	            CategoryMajor major = new CategoryMajor();
	            mainor.setMinorId(rs.getInt("minor_id"));
	            major.setMajorId(rs.getInt("parent_id"));
	            mainor.setParent(major);
	            mainor.setMinorName(rs.getString("minor_name"));
	            mainorList.add(mainor);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(conn, stmt, rs);
	    }
	    return mainorList;
	}
}